USE [comunion]
GO

/****** Object:  StoredProcedure [dbo].[USP_UpdateGL]    Script Date: 06/18/2010 17:32:55 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


--exec USP_UpdateGL '000-00', 'PI', 'RR-100008'

alter   PROCEDURE [dbo].[USP_UpdateGL]
   @cCompanyID VARCHAR(10),
   @cModule VARCHAR(10),
   @cTranNo VARCHAR(15)
AS
   DECLARE @param VARCHAR(150),
	   @cPrime VARCHAR(10),
	   @cmdTxt VARCHAR(2000)

SET @cPrime = CASE @cModule
		WHEN 'PI' THEN 'cTranNo'
		WHEN 'DR'  THEN 'cDRNo'
		WHEN 'SI'  THEN 'cInvNo'
		WHEN 'OR'  THEN 'cTranNo'
		WHEN 'DP'  THEN 'cTranNo'
		WHEN 'RTC' THEN 'cTranNo'
		WHEN 'POC' THEN 'cTranNo'
		WHEN 'RPC' THEN 'cTranNo'
		WHEN 'VOU' THEN 'cTranNo'
		WHEN 'ACR' THEN 'cTranNo'
		WHEN 'CMP' THEN 'cTranNo'
		WHEN 'CLR' THEN 'cTranNo'
		WHEN 'DBM' THEN 'cTranNo'
		WHEN 'CRM' THEN 'cTranNo'
		WHEN 'FAD' THEN 'cTranNo'
		WHEN 'PAY' THEN 'cTranNo'
	      END

SET @param = CASE @cModule
		WHEN 'PI' THEN ' where '+@cPrime+'='''+@cTranNo+''' and cCompanyID='''+@cCompanyID+''''
		WHEN 'DR'  THEN ' where '+@cPrime+'='''+@cTranNo+''' and cCompanyID='''+@cCompanyID+''''
		WHEN 'SI'  THEN ' where '+@cPrime+'='''+@cTranNo+''' and cCompanyID='''+@cCompanyID+''''
		WHEN 'OR'  THEN ' and '+@cPrime+'='''+@cTranNo+''' and cCompanyID='''+@cCompanyID+''''
		WHEN 'DP'  THEN ' and '+@cPrime+'='''+@cTranNo+''' and cCompanyID='''+@cCompanyID+''''
		WHEN 'RTC' THEN ' and '+@cPrime+'='''+@cTranNo+''' and cCompanyID='''+@cCompanyID+''''
		WHEN 'RPC' THEN ' and '+@cPrime+'='''+@cTranNo+''' and cCompanyID='''+@cCompanyID+''''
		WHEN 'VOU' THEN ' where '+@cPrime+'='''+@cTranNo+''' and cCompanyID='''+@cCompanyID+''''
		WHEN 'ACR' THEN ' and '+@cPrime+'='''+@cTranNo+''' and cCompanyID='''+@cCompanyID+''''
		WHEN 'CMP' THEN ' where '+@cPrime+'='''+@cTranNo+''' and cCompanyID='''+@cCompanyID+''''
		WHEN 'CLR' THEN ' where '+@cPrime+'='''+@cTranNo+''' and cCompanyID='''+@cCompanyID+''''
		WHEN 'DBM' THEN ' and '+@cPrime+'='''+@cTranNo+''' and cCompanyID='''+@cCompanyID+''''
		WHEN 'CRM' THEN ' and '+@cPrime+'='''+@cTranNo+''' and cCompanyID='''+@cCompanyID+''''
		WHEN 'FAD' THEN ' where '+@cPrime+'='''+@cTranNo+''' and cCompanyID='''+@cCompanyID+''''
		WHEN 'POC' THEN ' where '+@cPrime+'='''+@cTranNo+''' and cCompanyID='''+@cCompanyID+''''
	     END

IF (SELECT count(*) FROM GLActivity WHERE cCompanyID=@cCompanyID and cTranNo=@cTranNo and cModule=@cModule)>0 BEGIN
   DELETE FROM GLActivity WHERE cCompanyID=@cCompanyID and cTranNo=@cTranNo and cModule=@cModule
END
IF @cModule='PI' BEGIN
   DECLARE CurName CURSOR FOR
   SELECT 'INSERT INTO GLActivity SELECT * FROM (SELECT '''+@cCompanyID+''' AS cCompanyID, '''+@cModule+''' AS cModule, '''+@cTranNo+''' as cTranNo, ''' +
	CAST(dDate AS VARCHAR(20))+ ''' AS dDate, ''' + b.cAcctNo + ''' AS cAcctNo, ''' + c.cTitle+''' as cTitle, '+
	CASE WHEN b.cType='Debit' THEN '(' + b.cFormula + @param + ')' ELSE '''0''' END  + ' AS nDebit , ' +
	CASE WHEN b.cType='Credit' THEN '(' + b.cFormula + @Param + ')' ELSE '''0''' END + ' AS nCredit, ' +
	' ''0'' AS lInternal, ''0'' AS lExternal, ''' + UPPER('ALL') + ''' AS cTranCode , ''' +
	b.cCode + ''' AS cInterface, 0 as lPosted, null as dPosted) A WHERE (isnull(nDebit, 0)<>0 or isnull(nCredit,0)<>0)' AS cmdTxt
	FROM PURCHASE a INNER JOIN interface b ON UPPER('ALL')=b.cCriteria AND b.cModule = 'PI' and a.cCompanyID=@cCompanyID
	LEFT OUTER JOIN account c ON b.cAcctNo=c.cAcctNo and b.cCompanyID=c.cCompanyID
	WHERE a.cTranNo=@cTranNo AND b.cCompanyID=@cCompanyID

   OPEN CurName 
        FETCH NEXT FROM CurName INTO @cmdTxt
        WHILE @@FETCH_STATUS = 0 
              BEGIN
                 EXECUTE(@cmdTxt)
                 FETCH NEXT FROM CurName INTO @cmdTxt
              END
   DEALLOCATE CurName
END
ELSE IF @cModule='DR' BEGIN
   DECLARE CurName CURSOR FOR
   SELECT 'INSERT INTO GLActivity SELECT * FROM (SELECT '''+@cCompanyID+''' AS cCompanyID, '''+@cModule+''' AS cModule, '''+@cTranNo+''' as cTranNo, ''' +
	CAST(dDate AS VARCHAR(20))+ ''' AS dDate, ''' + b.cAcctNo + ''' AS cAcctNo, ''' + c.cTitle+''' as cTitle, '+ 
	CASE WHEN b.cType='Debit' THEN '(' + b.cFormula + @param + ')' ELSE '''0''' END  + ' AS nDebit , ' +
	CASE WHEN b.cType='Credit' THEN '(' + b.cFormula + @Param + ')' ELSE '''0''' END + ' AS nCredit, ' +
	' ''0'' AS lInternal, ''0'' AS lExternal, ''' + UPPER(a.cType) + ''' AS cTranCode , ''' +
	b.cCode + ''' AS cInterface, 0 as lPosted, null as dPosted) A WHERE (isnull(nDebit, 0)<>0 or isnull(nCredit,0)<>0)' AS cmdTxt
	FROM dr a INNER JOIN interface b ON UPPER(a.cType)=b.cCriteria AND b.cModule = 'DR' and a.cCompanyID=@cCompanyID
	LEFT OUTER JOIN account c ON b.cAcctNo=c.cAcctNo and b.cCompanyID=c.cCompanyID
	WHERE a.cDRNo=@cTranNo AND b.cCompanyID=@cCompanyID

   OPEN CurName 
        FETCH NEXT FROM CurName INTO @cmdTxt
        WHILE @@FETCH_STATUS = 0 
              BEGIN
                 EXECUTE(@cmdTxt)
                 FETCH NEXT FROM CurName INTO @cmdTxt
              END
   DEALLOCATE CurName
END
ELSE IF @cModule='SI' BEGIN
   DECLARE CurName CURSOR FOR
   SELECT 'INSERT INTO GLActivity SELECT * FROM (SELECT '''+@cCompanyID+''' AS cCompanyID, '''+@cModule+''' AS cModule, '''+@cTranNo+''' as cTranNo, ''' +
	CAST(dDate AS VARCHAR(20))+ ''' AS dDate, ''' + b.cAcctNo + ''' AS cAcctNo, ''' + c.cTitle+''' as cTitle, ' +
	CASE WHEN b.cType='Debit' THEN '(' + b.cFormula + @param + ')' ELSE '''0''' END  + ' AS nDebit , ' +
	CASE WHEN b.cType='Credit' THEN '(' + b.cFormula + @Param + ')' ELSE '''0''' END + ' AS nCredit, ' +
	' ''0'' AS lInternal, ''0'' AS lExternal, ''' + UPPER(a.cType) + ''' AS cTranCode , ''' +
	b.cCode + ''' AS cInterface, 0 as lPosted, null as dPosted) A WHERE (isnull(nDebit, 0)<>0 or isnull(nCredit,0)<>0) ' AS cmdTxt
	FROM sales a INNER JOIN interface b ON UPPER(a.cType)=b.cCriteria AND b.cModule = 'SI' and a.cCompanyID=@cCompanyID
	LEFT OUTER JOIN account c ON b.cAcctNo=c.cAcctNo and b.cCompanyID=c.cCompanyID
	WHERE a.cInvNo=@cTranNo AND b.cCompanyID=@cCompanyID

   OPEN CurName 
        FETCH NEXT FROM CurName INTO @cmdTxt
        WHILE @@FETCH_STATUS = 0 
              BEGIN
                 EXECUTE(@cmdTxt)
                 FETCH NEXT FROM CurName INTO @cmdTxt
              END
   DEALLOCATE CurName
END
ELSE IF @cModule='AR' BEGIN
   INSERT INTO GLActivity
	SELECT @cCompanyID as cCompanyID, 'AR' as cModule, a.cTranNo, a.dDate, b.cAcctNo, c.cTitle, b.nDebit, b.nCredit,
		'0' as lInternal, '0' as lExternal, a.cType as cTranCode, 'NO INTERFACE' as cInterface, 0 as lPosted,
		null as dPosted from ar_t b
		inner join ar a on a.cTranNo=b.cTranNo and b.cCompanyID=@cCompanyID
		LEFT OUTER JOIN account c ON b.cAcctNo=c.cAcctNo and b.cCompanyID=c.cCompanyID
		where a.cCompanyID=@cCompanyID and a.cTranNo=@cTranNo
END
ELSE IF @cModule='AP' BEGIN
   INSERT INTO GLActivity
	SELECT @cCompanyID as cCompanyID, 'AP' as cModule, a.cTranNo, a.dDate, b.cAcctNo, c.cTitle, b.nDebit, b.nCredit,
		'0' as lInternal, '0' as lExternal, a.cType as cTranCode, 'NO INTERFACE' as cInterface, 0 as lPosted,
		null as dPosted from ap_t b
		inner join ap a on a.cTranNo=b.cTranNo and b.cCompanyID=@cCompanyID
		LEFT OUTER JOIN account c ON b.cAcctNo=c.cAcctNo and b.cCompanyID=c.cCompanyID
		where a.cCompanyID=@cCompanyID and a.cTranNo=@cTranNo
END
ELSE IF @cModule='CMP' BEGIN
   DECLARE CurName CURSOR FOR
   SELECT 'INSERT INTO GLActivity SELECT * FROM (SELECT '''+@cCompanyID+''' AS cCompanyID, '''+@cModule+''' AS cModule, '''+@cTranNo+''' as cTranNo, ''' +
	CAST(dDate AS VARCHAR(20))+ ''' AS dDate, ''' + b.cAcctNo + ''' AS cAcctNo, ''' + c.cTitle+''' as cTitle, '+
	CASE WHEN b.cType='Debit' THEN '(' + b.cFormula + @param + ')' ELSE '''0''' END  + ' AS nDebit , ' +
	CASE WHEN b.cType='Credit' THEN '(' + b.cFormula + @Param + ')' ELSE '''0''' END + ' AS nCredit, ' +
	' ''0'' AS lInternal, ''0'' AS lExternal, ''' + UPPER('ALL CM') + ''' AS cTranCode , ''' +
	b.cCode + ''' AS cInterface, 0 as lPosted, null as dPosted) A WHERE (isnull(nDebit, 0)<>0 or isnull(nCredit,0)<>0) ' AS cmdTxt
	FROM cm a INNER JOIN interface b ON UPPER('ALLCMP')=b.cCriteria AND b.cModule = 'CMP' and a.cCompanyID=@cCompanyID
	LEFT OUTER JOIN account c ON b.cAcctNo=c.cAcctNo and b.cCompanyID=c.cCompanyID
	WHERE a.cTranNo=@cTranNo AND b.cCompanyID=@cCompanyID

   OPEN CurName 
        FETCH NEXT FROM CurName INTO @cmdTxt
        WHILE @@FETCH_STATUS = 0 
              BEGIN
                 EXECUTE(@cmdTxt)
                 FETCH NEXT FROM CurName INTO @cmdTxt
              END
   DEALLOCATE CurName
   INSERT INTO GLActivity
	SELECT @cCompanyID as cCompanyID, 'CMP' as cModule, a.cTranNo, a.dDate, b.cAcctNo, c.cTitle,
		b.nAmount as nDebit, '0' as nCredit, '0' as lInternal, '0' as lExternal, 'CMP' as cTranCode, 
		'NO INTERFACE' as cInterface, 0 as lPosted, null as dPosted from cm_t b
		inner join cm a on a.cTranNo=b.cTranNo 
		LEFT OUTER JOIN account c ON b.cAcctNo=c.cAcctNo and b.cCompanyID=c.cCompanyID
		where a.cCompanyID=@cCompanyID and a.cTranNo=@cTranNo 
END     
ELSE IF @cModule='OR' BEGIN
   DECLARE CurName CURSOR FOR
   SELECT 'INSERT INTO GLActivity SELECT * FROM (SELECT '''+@cCompanyID+''' AS cCompanyID, '''+@cModule+''' AS cModule, '''+@cTranNo+''' as cTranNo, ''' +
	CAST(dDate AS VARCHAR(20))+ ''' AS dDate, ''' + b.cAcctNo + ''' AS cAcctNo, ''' + c.cTitle+''' as cTitle, ' +
	CASE WHEN b.cType='Debit' THEN 'ISNULL((' + b.cFormula + @param + '),0)' ELSE '''0''' END  + ' AS nDebit , ' +
	CASE WHEN b.cType='Credit' THEN 'ISNULL((' + b.cFormula + @Param + '),0)' ELSE '''0''' END + ' AS nCredit, ' +
	' ''0'' AS lInternal, ''0'' AS lExternal, ''' + UPPER('ALL') + ''' AS cTranCode , ''' +
	b.cCode + ''' AS cInterface, 0 as lPosted, null as dPosted) A WHERE (isnull(nDebit, 0)<>0 or isnull(nCredit,0)<>0) ' AS cmdTxt
	FROM pr a INNER JOIN interface b ON UPPER('ALL')=b.cCriteria AND b.cModule = 'OR' and a.cCompanyID=@cCompanyID
	LEFT OUTER JOIN account c ON b.cAcctNo=c.cAcctNo and b.cCompanyID=c.cCompanyID
	WHERE a.cTranNo=@cTranNo AND b.cCompanyID=@cCompanyID

   OPEN CurName 
        FETCH NEXT FROM CurName INTO @cmdTxt
        WHILE @@FETCH_STATUS = 0 
              BEGIN
                 EXECUTE(@cmdTxt)
                 FETCH NEXT FROM CurName INTO @cmdTxt
              END
   DEALLOCATE CurName
   IF (SELECT COUNT(*) FROM PR_O WHERE cCompanyID=@cCompanyID and cTranNo=@cTranNo)>0 BEGIN
      INSERT INTO GLActivity
	SELECT @cCompanyID as cCompanyID, 'OR' as cModule, a.cTranNo, a.dDate, b.cAcctNo, c.cTitle,
		case b.cType when 'Debit' then b.nAmount else '0' end as nDebit, 
		case b.cType when 'Credit' then b.nAmount else '0' end as nCredit,
		'0' as lInternal, '0' as lExternal, b.cType as cTranCode, 'NO INTERFACE' as cInterface,
		0 as lPosted, null as dPosted from pr_o b
		inner join pr a on a.cTranNo=b.cTranNo 
		LEFT OUTER JOIN account c ON b.cAcctNo=c.cAcctNo and b.cCompanyID=c.cCompanyID
		where a.cCompanyID=@cCompanyID and a.cTranNo=@cTranNo      
   END
END
ELSE IF @cModule='GL' BEGIN
   INSERT INTO GLActivity
	SELECT @cCompanyID as cCompanyID, 'GL' as cModule, a.cTranNo, a.dDate, b.cAcctNo, c.cTitle, b.nDebit, b.nCredit,
		'0' as lInternal, '0' as lExternal, a.cCode as cTranCode, 'NO INTERFACE' as cInterface, 0 as lPosted,
		null as dPosted from gl_t b
		inner join gl a on a.cTranNo=b.cTranNo 
		LEFT OUTER JOIN account c ON b.cAcctNo=c.cAcctNo and b.cCompanyID=c.cCompanyID
		where a.cCompanyID=@cCompanyID and a.cTranNo=@cTranNo
END
ELSE IF @cModule='DP' BEGIN
   DECLARE CurName CURSOR FOR
   SELECT 'INSERT INTO GLActivity SELECT * FROM (SELECT '''+@cCompanyID+''' AS cCompanyID, '''+@cModule+''' AS cModule, '''+@cTranNo+''' as cTranNo, ''' +
	CAST(dDate AS VARCHAR(20))+ ''' AS dDate, ''' + b.cAcctNo + ''' AS cAcctNo, ''' + c.cTitle+''' as cTitle, ' +
	CASE WHEN b.cType='Debit' THEN 'ISNULL((' + b.cFormula + @param + '),0)' ELSE '''0''' END  + ' AS nDebit , ' +
	CASE WHEN b.cType='Credit' THEN 'ISNULL((' + b.cFormula + @Param + '),0)' ELSE '''0''' END + ' AS nCredit, ' +
	' ''0'' AS lInternal, ''0'' AS lExternal, ''' + UPPER('ALL') + ''' AS cTranCode , ''' +
	b.cCode + ''' AS cInterface, 0 as lPosted, null as dPosted) A WHERE (isnull(nDebit, 0)<>0 or isnull(nCredit,0)<>0) ' AS cmdTxt
	FROM deposit a INNER JOIN interface b ON UPPER('ALLDEP')=b.cCriteria AND b.cModule = 'DP' and a.cCompanyID=@cCompanyID
	LEFT OUTER JOIN account c ON b.cAcctNo=c.cAcctNo and b.cCompanyID=c.cCompanyID
	WHERE a.cTranNo=@cTranNo AND b.cCompanyID=@cCompanyID

   OPEN CurName 
        FETCH NEXT FROM CurName INTO @cmdTxt
        WHILE @@FETCH_STATUS = 0 
              BEGIN
                 EXECUTE(@cmdTxt)
                 FETCH NEXT FROM CurName INTO @cmdTxt
              END
   DEALLOCATE CurName
   INSERT INTO GLActivity
	SELECT @cCompanyID as cCompanyID, 'DP' as cModule, a.cTranNo, a.dDate, a.cAcctNo, b.cTitle, a.nCash+a.nCheck as nDebit,
		0 as nCredit, '0' as lInternal, '0' as lExternal, 'ALL' as cTranCode, 'NO INTERFACE' as cInterface, 
		0 as lPosted, null as dPosted from deposit a
		LEFT OUTER JOIN account b ON a.cAcctNo=b.cAcctNo and a.cCompanyID=b.cCompanyID
		where a.cCompanyID=@cCompanyID and a.cTranNo=@cTranNo
END
ELSE IF @cModule='RTC' BEGIN
   DECLARE CurName CURSOR FOR
   SELECT 'INSERT INTO GLActivity SELECT * FROM (SELECT '''+@cCompanyID+''' AS cCompanyID, '''+@cModule+''' AS cModule, '''+@cTranNo+''' as cTranNo, ''' +
	CAST(dDate AS VARCHAR(20))+ ''' AS dDate, ''' + b.cAcctNo + ''' AS cAcctNo, ''' + c.cTitle+''' as cTitle, ' +
	CASE WHEN b.cType='Debit' THEN 'ISNULL((' + b.cFormula + @param + '),0)' ELSE '''0''' END  + ' AS nDebit , ' +
	CASE WHEN b.cType='Credit' THEN 'ISNULL((' + b.cFormula + @Param + '),0)' ELSE '0' END + ' AS nCredit, ' +
	' ''0'' AS lInternal, ''0'' AS lExternal, ''' + UPPER('ALL') + ''' AS cTranCode , ''' +
	b.cCode + ''' AS cInterface, 0 as lPosted, null as dPosted) A WHERE (isnull(nDebit, 0)<>0 or isnull(nCredit,0)<>0) ' AS cmdTxt
	FROM bounced a INNER JOIN interface b ON UPPER('ALLRTC')=b.cCriteria AND b.cModule = 'RTC' and a.cCompanyID=@cCompanyID
	LEFT OUTER JOIN account c ON b.cAcctNo=c.cAcctNo and b.cCompanyID=c.cCompanyID
	WHERE a.cTranNo=@cTranNo AND b.cCompanyID=@cCompanyID

   OPEN CurName 
        FETCH NEXT FROM CurName INTO @cmdTxt
        WHILE @@FETCH_STATUS = 0 
              BEGIN
                 EXECUTE(@cmdTxt)
                 FETCH NEXT FROM CurName INTO @cmdTxt
              END
   DEALLOCATE CurName
   INSERT INTO GLActivity
	SELECT @cCompanyID as cCompanyID, 'RTC' as cModule, a.cTranNo, a.dDate, a.cAcctNo, b.cTitle, 0 as nDebit,
		a.nRTC+a.nPull as nCredit, '0' as lInternal, '0' as lExternal, 'ALL' as cTranCode, 'NO INTERFACE' as cInterface, 
		0 as lPosted, null as dPosted from bounced a
		LEFT OUTER JOIN account b ON a.cAcctNo=b.cAcctNo and a.cCompanyID=b.cCompanyID
		where a.cCompanyID=@cCompanyID and a.cTranNo=@cTranNo
END
ELSE IF @cModule='POC' BEGIN
   DECLARE CurName CURSOR FOR
   SELECT 'INSERT INTO GLActivity SELECT * FROM (SELECT '''+@cCompanyID+''' AS cCompanyID, '''+@cModule+''' AS cModule, '''+@cTranNo+''' as cTranNo, ''' +
	CAST(dDate AS VARCHAR(20))+ ''' AS dDate, ''' + b.cAcctNo + ''' AS cAcctNo, ''' + c.cTitle+''' as cTitle, ' +
	CASE WHEN b.cType='Debit' THEN 'ISNULL((' + b.cFormula + @param + '),0)' ELSE '''0''' END  + ' AS nDebit , ' +
	CASE WHEN b.cType='Credit' THEN 'ISNULL((' + b.cFormula + @Param + '),0)' ELSE '0' END + ' AS nCredit, ' +
	' ''0'' AS lInternal, ''0'' AS lExternal, ''' + UPPER('ALL') + ''' AS cTranCode , ''' +
	b.cCode + ''' AS cInterface, 0 as lPosted, null as dPosted) A WHERE (isnull(nDebit, 0)<>0 or isnull(nCredit,0)<>0) ' AS cmdTxt
	FROM PULLED a INNER JOIN interface b ON UPPER('ALLPOC')=b.cCriteria AND b.cModule = 'POC' and a.cCompanyID=@cCompanyID
	LEFT OUTER JOIN account c ON b.cAcctNo=c.cAcctNo and b.cCompanyID=c.cCompanyID
	WHERE a.cTranNo=@cTranNo AND b.cCompanyID=@cCompanyID

   OPEN CurName 
        FETCH NEXT FROM CurName INTO @cmdTxt
        WHILE @@FETCH_STATUS = 0 
              BEGIN
                 EXECUTE(@cmdTxt)
                 FETCH NEXT FROM CurName INTO @cmdTxt
              END
   DEALLOCATE CurName
   INSERT INTO GLActivity
	SELECT @cCompanyID as cCompanyID, 'POC' as cModule, a.cTranNo, a.dDate, a.cAcctNo, b.cTitle, a.nGross as nDebit,
		0 as nCredit, '0' as lInternal, '0' as lExternal, 'ALL' as cTranCode, 'NO INTERFACE' as cInterface, 
		0 as lPosted, null as dPosted from PULLED a
		LEFT OUTER JOIN account b ON a.cAcctNo=b.cAcctNo and a.cCompanyID=b.cCompanyID
		where a.cCompanyID=@cCompanyID and a.cTranNo=@cTranNo
END
ELSE IF @cModule='RPC' BEGIN
   DECLARE CurName CURSOR FOR
   SELECT 'INSERT INTO GLActivity SELECT * FROM (SELECT '''+@cCompanyID+''' AS cCompanyID, '''+@cModule+''' AS cModule, '''+@cTranNo+''' as cTranNo, ''' +
	CAST(dDate AS VARCHAR(20))+ ''' AS dDate, ''' + b.cAcctNo + ''' AS cAcctNo, ''' + c.cTitle+''' as cTitle, ' +
	CASE WHEN b.cType='Debit' THEN 'ISNULL((' + b.cFormula + @param + '),0)' ELSE '''0''' END  + ' AS nDebit , ' +
	CASE WHEN b.cType='Credit' THEN 'ISNULL((' + b.cFormula + @Param + '),0)' ELSE '0' END + ' AS nCredit, ' +
	' ''0'' AS lInternal, ''0'' AS lExternal, ''' + UPPER('ALL') + ''' AS cTranCode , ''' +
	b.cCode + ''' AS cInterface, 0 as lPosted, null as dPosted) A WHERE (isnull(nDebit, 0)<>0 or isnull(nCredit,0)<>0) ' AS cmdTxt
	FROM REPLACED a INNER JOIN interface b ON UPPER('ALLRPC')=b.cCriteria AND b.cModule = 'RPC' and a.cCompanyID=@cCompanyID
	LEFT OUTER JOIN account c ON b.cAcctNo=c.cAcctNo and b.cCompanyID=c.cCompanyID
	WHERE a.cTranNo=@cTranNo AND b.cCompanyID=@cCompanyID

   OPEN CurName 
        FETCH NEXT FROM CurName INTO @cmdTxt
        WHILE @@FETCH_STATUS = 0 
              BEGIN
                 EXECUTE(@cmdTxt)
                 FETCH NEXT FROM CurName INTO @cmdTxt
              END
   DEALLOCATE CurName
   INSERT INTO GLActivity
	SELECT @cCompanyID as cCompanyID, 'RPC' as cModule, a.cTranNo, a.dDate, a.cAcctNo, b.cTitle, 0 as nDebit,
		a.nCash+a.nCHK as nCredit, '0' as lInternal, '0' as lExternal, 'ALL' as cTranCode, 'NO INTERFACE' as cInterface, 
		0 as lPosted, null as dPosted from REPLACED a
		LEFT OUTER JOIN account b ON a.cAcctNo=b.cAcctNo and a.cCompanyID=b.cCompanyID
		where a.cCompanyID=@cCompanyID and a.cTranNo=@cTranNo
END
ELSE IF @cModule='VOU' BEGIN
   DECLARE CurName CURSOR FOR
   SELECT 'INSERT INTO GLActivity SELECT * FROM (SELECT '''+@cCompanyID+''' AS cCompanyID, '''+@cModule+''' AS cModule, '''+@cTranNo+''' as cTranNo, ''' +
	CAST(dDate AS VARCHAR(20))+ ''' AS dDate, ''' + b.cAcctNo + ''' AS cAcctNo, ''' + c.cTitle+''' as cTitle, ' +
	CASE WHEN b.cType='Debit' THEN 'ISNULL((' + b.cFormula + @param + '),0)' ELSE '''0''' END  + ' AS nDebit , ' +
	CASE WHEN b.cType='Credit' THEN 'ISNULL((' + b.cFormula + @Param + '),0)' ELSE '''0''' END + ' AS nCredit, ' +
	' ''0'' AS lInternal, ''0'' AS lExternal, ''' + UPPER(b.cCode) + ''' AS cTranCode , ''' +
	b.cCode + ''' AS cInterface, 0 as lPosted, null as dPosted) A WHERE (isnull(nDebit, 0)<>0 or isnull(nCredit,0)<>0) ' AS cmdTxt
	FROM voucher a INNER JOIN interface b ON UPPER('ALLVOU')=b.cCriteria AND b.cModule = 'VOU' and a.cCompanyID=@cCompanyID
	LEFT OUTER JOIN account c ON b.cAcctNo=c.cAcctNo and b.cCompanyID=c.cCompanyID
	WHERE a.cTranNo=@cTranNo AND b.cCompanyID=@cCompanyID

   OPEN CurName 
        FETCH NEXT FROM CurName INTO @cmdTxt
        WHILE @@FETCH_STATUS = 0 
              BEGIN
                 EXECUTE(@cmdTxt)
                 FETCH NEXT FROM CurName INTO @cmdTxt
              END
   DEALLOCATE CurName
   IF (SELECT COUNT(*) FROM VOUCHER_OPEX WHERE cCompanyID=@cCompanyID and cTranNo=@cTranNo)>0 BEGIN
      INSERT INTO GLActivity
	SELECT @cCompanyID as cCompanyID, 'VOU' as cModule, a.cTranNo, a.dDate, b.cAcctNo, c.cTitle, b.nDebit, b.nCredit,
		'0' as lInternal, '0' as lExternal, 'OPEX' as cTranCode, 'NO INTERFACE' as cInterface, 0 as lPosted,
		null as dPosted from voucher_opex b
		inner join voucher a on a.cTranNo=b.cTranNo 
		LEFT OUTER JOIN account c ON b.cAcctNo=c.cAcctNo and b.cCompanyID=c.cCompanyID
		where a.cCompanyID=@cCompanyID and a.cTranNo=@cTranNo
   END
   IF (SELECT COUNT(*) FROM VOUCHER_NONTRADE WHERE cCompanyID=@cCompanyID and cTranNo=@cTranNo)>0 BEGIN
      INSERT INTO GLActivity
	SELECT @cCompanyID as cCompanyID, 'VOU' as cModule, a.cTranNo, a.dDate, b.cAcctNo, c.cTitle, b.nApplied as nDebit, 0 as nCredit,
		'0' as lInternal, '0' as lExternal, 'NONTRADE' as cTranCode, 'NO INTERFACE' as cInterface, 0 as lPosted,
		null as dPosted from voucher a inner join (select a.*, b.cAcctNo from voucher_nontrade a 
			inner join payables_account b on a.cPayableID=b.cTranNo and b.cType='Payables' and a.cCompanyID=b.cCompanyID) b on a.cTranNo=b.cTranNo
			LEFT OUTER JOIN account c ON b.cAcctNo=c.cAcctNo and b.cCompanyID=c.cCompanyID
			where a.cCompanyID=@cCompanyID and a.cTranNo=@cTranNo
   END
END
ELSE IF @cModule='CLR' BEGIN

   DECLARE CurName CURSOR FOR
   SELECT 'INSERT INTO GLActivity SELECT * FROM (SELECT '''+@cCompanyID+''' AS cCompanyID, '''+@cModule+''' AS cModule, '''+@cTranNo+''' as cTranNo, ''' +
	CAST(dDate AS VARCHAR(20))+ ''' AS dDate, ''' + b.cAcctNo + ''' AS cAcctNo, ''' + c.cTitle+''' as cTitle, ' +
	CASE WHEN b.cType='Debit' THEN 'ISNULL((' + b.cFormula + @param + '),0)' ELSE '''0''' END  + ' AS nDebit , ' +
	CASE WHEN b.cType='Credit' THEN 'ISNULL((' + b.cFormula + @Param + '),0)' ELSE '''0''' END + ' AS nCredit, ' +
	' ''0'' AS lInternal, ''0'' AS lExternal, ''' + UPPER('ALL') + ''' AS cTranCode , ''' +
	b.cCode + ''' AS cInterface, 0 as lPosted, null as dPosted) A WHERE (isnull(nDebit, 0)<>0 or isnull(nCredit,0)<>0) ' AS cmdTxt
	FROM cleared a INNER JOIN interface b ON UPPER('ALLCLR')=b.cCriteria AND b.cModule = 'CLR' and a.cCompanyID=@cCompanyID
	LEFT OUTER JOIN account c ON b.cAcctNo=c.cAcctNo and b.cCompanyID=c.cCompanyID
	WHERE a.cTranNo=@cTranNo AND b.cCompanyID=@cCompanyID

   OPEN CurName 
        FETCH NEXT FROM CurName INTO @cmdTxt
        WHILE @@FETCH_STATUS = 0 
              BEGIN
                 EXECUTE(@cmdTxt)
                 FETCH NEXT FROM CurName INTO @cmdTxt
              END
   DEALLOCATE CurName
   INSERT INTO GLActivity
	SELECT @cCompanyID as cCompanyID, 'CLR' as cModule, a.cTranNo, a.dDate, a.cAcctNo, b.cTitle, 0 as nDebit,
		a.nGross as nCredit, '0' as lInternal, '0' as lExternal, 'ALL' as cTranCode, 'NO INTERFACE' as cInterface, 
		0 as lPosted, null as dPosted from cleared a
		LEFT OUTER JOIN account b ON a.cAcctNo=b.cAcctNo and a.cCompanyID=b.cCompanyID
		where a.cCompanyID=@cCompanyID and a.cTranNo=@cTranNo
END
ELSE IF @cModule='ACR' BEGIN
   DECLARE CurName CURSOR FOR
   SELECT 'INSERT INTO GLActivity SELECT * FROM (SELECT '''+@cCompanyID+''' AS cCompanyID, '''+@cModule+''' AS cModule, '''+@cTranNo+''' as cTranNo, ''' +
	CAST(dDate AS VARCHAR(20))+ ''' AS dDate, ''' + b.cAcctNo + ''' AS cAcctNo, ''' + c.cTitle+''' as cTitle, ' +
	CASE WHEN b.cType='Debit' THEN 'ISNULL((' + b.cFormula + @param + '),0)' ELSE '''0''' END  + ' AS nDebit , ' +
	CASE WHEN b.cType='Credit' THEN 'ISNULL((' + b.cFormula + @Param + '),0)' ELSE '''0''' END + ' AS nCredit, ' +
	' ''0'' AS lInternal, ''0'' AS lExternal, ''' + UPPER('ALL ACR') + ''' AS cTranCode , ''' +
	b.cCode + ''' AS cInterface, 0 as lPosted, null as dPosted) A WHERE (isnull(nDebit, 0)<>0 or isnull(nCredit,0)<>0) ' AS cmdTxt
	FROM intar a INNER JOIN interface b ON UPPER('ALLACR')=b.cCriteria AND b.cModule = 'ACR' and a.cCompanyID=@cCompanyID
	LEFT OUTER JOIN account c ON b.cAcctNo=c.cAcctNo and b.cCompanyID=c.cCompanyID
	WHERE a.cTranNo=@cTranNo AND b.cCompanyID=@cCompanyID

   OPEN CurName 
        FETCH NEXT FROM CurName INTO @cmdTxt
        WHILE @@FETCH_STATUS = 0 
              BEGIN
                 EXECUTE(@cmdTxt)
                 FETCH NEXT FROM CurName INTO @cmdTxt
              END
   DEALLOCATE CurName
   IF (SELECT COUNT(*) FROM INTAR_T WHERE cCompanyID=@cCompanyID and cTranNo=@cTranNo)>0 BEGIN
      INSERT INTO GLActivity
	SELECT @cCompanyID as cCompanyID, 'ACR' as cModule, a.cTranNo, a.dDate, b.cAcctNo, c.cTitle,
		case b.cType when 'Debit' then b.nAmount else '0' end as nDebit, 
		case b.cType when 'Credit' then b.nAmount else '0' end as nCredit,
		'0' as lInternal, '0' as lExternal, b.cType as cTranCode, 'NO INTERFACE' as cInterface,
		0 as lPosted, null as dPosted from intar_t b
		inner join intar a on a.cTranNo=b.cTranNo
		LEFT OUTER JOIN account c ON b.cAcctNo=c.cAcctNo and b.cCompanyID=c.cCompanyID
		where a.cCompanyID=@cCompanyID and a.cTranNo=@cTranNo      
   END
END
ELSE IF @cModule='DBM' BEGIN
   DECLARE CurName CURSOR FOR
   SELECT 'INSERT INTO GLActivity SELECT * FROM (SELECT '''+@cCompanyID+''' AS cCompanyID, '''+@cModule+''' AS cModule, '''+@cTranNo+''' as cTranNo, ''' +
	CAST(dDate AS VARCHAR(20))+ ''' AS dDate, ''' + b.cAcctNo + ''' AS cAcctNo, ''' + c.cTitle+''' as cTitle, '+
	CASE WHEN b.cType='Debit' THEN '(' + b.cFormula + @param + ')' ELSE '''0''' END  + ' AS nDebit , ' +
	CASE WHEN b.cType='Credit' THEN '(' + b.cFormula + @Param + ')' ELSE '''0''' END + ' AS nCredit, ' +
	' ''0'' AS lInternal, ''0'' AS lExternal, ''' + UPPER('ALL DM') + ''' AS cTranCode , ''' +
	b.cCode + ''' AS cInterface, 0 as lPosted, null as dPosted) A WHERE (isnull(nDebit, 0)<>0 or isnull(nCredit,0)<>0) ' AS cmdTxt
	FROM DEBIT a INNER JOIN interface b ON UPPER('ALLDBM')=b.cCriteria AND b.cModule = 'DBM' and a.cCompanyID=b.cCompanyID
	LEFT OUTER JOIN account c ON b.cAcctNo=c.cAcctNo and a.cCompanyID=c.cCompanyID
	WHERE a.cTranNo=@cTranNo AND a.cCompanyID=@cCompanyID and c.cCompanyID=@cCompanyID

   OPEN CurName 
        FETCH NEXT FROM CurName INTO @cmdTxt
        WHILE @@FETCH_STATUS = 0 
              BEGIN
                 EXECUTE(@cmdTxt)
                 FETCH NEXT FROM CurName INTO @cmdTxt
              END
   DEALLOCATE CurName
   INSERT INTO GLActivity
	SELECT @cCompanyID as cCompanyID, 'DBM' as cModule, a.cTranNo, a.dDate, b.cAcctNo, c.cTitle,
		b.nAmount as nDebit, '0' as nCredit, '0' as lInternal, '0' as lExternal, 'DBM' as cTranCode, 
		'NO INTERFACE' as cInterface, 0 as lPosted, null as dPosted from DEBIT_CMDM b
		inner join DEBIT a on a.cTranNo=b.cTranNo and a.cCompanyID=b.cCompanyID
		LEFT OUTER JOIN account c ON b.cAcctNo=c.cAcctNo and b.cCompanyID=c.cCompanyID
		where a.cCompanyID=@cCompanyID and a.cTranNo=@cTranNo and c.cCompanyID=@cCompanyID
END
ELSE IF @cModule='CRM' BEGIN
   DECLARE CurName CURSOR FOR
   SELECT 'INSERT INTO GLActivity SELECT * FROM (SELECT '''+@cCompanyID+''' AS cCompanyID, '''+@cModule+''' AS cModule, '''+@cTranNo+''' as cTranNo, ''' +
	CAST(dDate AS VARCHAR(20))+ ''' AS dDate, ''' + b.cAcctNo + ''' AS cAcctNo, ''' + c.cTitle+''' as cTitle, '+
	CASE WHEN b.cType='Debit' THEN '(' + b.cFormula + @param + ')' ELSE '''0''' END  + ' AS nDebit , ' +
	CASE WHEN b.cType='Credit' THEN '(' + b.cFormula + @Param + ')' ELSE '''0''' END + ' AS nCredit, ' +
	' ''0'' AS lInternal, ''0'' AS lExternal, ''' + UPPER('ALL CM') + ''' AS cTranCode , ''' +
	b.cCode + ''' AS cInterface, 0 as lPosted, null as dPosted) A WHERE (isnull(nDebit, 0)<>0 or isnull(nCredit,0)<>0)' AS cmdTxt
	FROM CREDIT a INNER JOIN interface b ON UPPER('ALLCRM')=b.cCriteria AND b.cModule = 'CRM' and a.cCompanyID=b.cCompanyID
	LEFT OUTER JOIN account c ON b.cAcctNo=c.cAcctNo and a.cCompanyID=c.cCompanyID
	WHERE a.cTranNo=@cTranNo AND a.cCompanyID=@cCompanyID and c.cCompanyID=@cCompanyID

   OPEN CurName 
        FETCH NEXT FROM CurName INTO @cmdTxt
        WHILE @@FETCH_STATUS = 0 
              BEGIN
                 EXECUTE(@cmdTxt)
                 FETCH NEXT FROM CurName INTO @cmdTxt
              END
   DEALLOCATE CurName
   INSERT INTO GLActivity
	SELECT @cCompanyID as cCompanyID, 'CRM' as cModule, a.cTranNo, a.dDate, b.cAcctNo, c.cTitle,
		'0' as nDebit, b.nAmount as nCredit, '0' as lInternal, '0' as lExternal, 'CRM' as cTranCode, 
		'NO INTERFACE' as cInterface, 0 as lPosted, null as dPosted from CREDIT_CMDM b
		inner join CREDIT a on a.cTranNo=b.cTranNo and a.cCompanyID=b.cCompanyID
		LEFT OUTER JOIN account c ON b.cAcctNo=c.cAcctNo and b.cCompanyID=c.cCompanyID
		where a.cCompanyID=@cCompanyID and a.cTranNo=@cTranNo and c.cCompanyID=@cCompanyID
END
ELSE IF @cModule='FAD' BEGIN
   INSERT INTO GLActivity
	SELECT @cCompanyID, 'FAD' as cModule, a.cTranNo, a.dDate, b.cAcctNo, c.cTitle, b.nDebit, b.nCredit,
		0 as lInternal, 0 as lExternal, 'ALLFAD' as cTranCode, 'NO INTERFACE' as cInterface, 0 as lPosted,
		null as dPosted from (
		select cCompanyID, cTranNo, cDepreAcct as cAcctNo, 0 as nDebit, nMoDepreciation as nCredit
		from fixedassetdepre_t where cTranNo=@cTranNo and cCompanyID=@cCompanyID
		union all
		select cCompanyID, cTranNo, cExpAcct as cAcctNo, nMoDepreciation as nDebit, 0 as nCredit
		from fixedassetdepre_t where cTranNo=@cTranNo and cCompanyID=@cCompanyID
		) b inner join FIXEDASSETDEPRE a on a.cTranNo=b.cTranNo and a.cCompanyID=b.cCompanyID
		LEFT OUTER JOIN ACCOUNT c ON b.cAcctNo=c.cAcctNo and b.cCompanyID=c.cCompanyID
END
ELSE IF @cModule='EWT' BEGIN
   INSERT INTO GLActivity
	SELECT @cCompanyID as cCompanyID, 'EWT' as cModule, a.cTranNo, a.dDate, b.cAcctNo, c.cTitle, b.nAmount as nDebit, 0 as nCredit,
		'0' as lInternal, '0' as lExternal, 'ALLEWT' as cTranCode, 'NO INTERFACE' as cInterface, 0 as lPosted,
		null as dPosted from cm_ewtevat_t b
		inner join cm_ewtevat a on a.cTranNo=b.cTranNo and b.cCompanyID=@cCompanyID
		LEFT OUTER JOIN account c ON b.cAcctNo=c.cAcctNo and b.cCompanyID=c.cCompanyID
		where a.cCompanyID=@cCompanyID and a.cTranNo=@cTranNo
   INSERT INTO GLActivity
	SELECT @cCompanyID as cCompanyID, 'EWT' as cModule, a.cTranNo, a.dDate, 
		(select cValue from system_option where cCompanyID=@cCompanyID and cCode='DEF_EWT') as cAcctNo, 
		c.cTitle, 0 as nDebit, b.nAmount as nCredit,
		'0' as lInternal, '0' as lExternal, 'ALLEWT' as cTranCode, 'NO INTERFACE' as cInterface, 0 as lPosted,
		null as dPosted from cm_ewtevat_t b
		inner join cm_ewtevat a on a.cTranNo=b.cTranNo and b.cCompanyID=@cCompanyID
		LEFT OUTER JOIN account c ON (select cValue from system_option where cCompanyID=@cCompanyID and cCode='DEF_EWT')=c.cAcctNo and b.cCompanyID=c.cCompanyID
		where a.cCompanyID=@cCompanyID and a.cTranNo=@cTranNo
END
ELSE IF @cModule='PAY' BEGIN
   INSERT INTO GLActivity
	SELECT @cCompanyID as cCompanyID, 'PAY' as cModule, a.cTranNo, a.dDate, b.cAcctNo, c.cTitle, b.nDebit, b.nCredit,
		'0' as lInternal, '0' as lExternal, b.cType as cTranCode, 'NO INTERFACE' as cInterface, 0 as lPosted,
		null as dPosted from payables_account b
		inner join payables a on a.cTranNo=b.cTranNo 
		LEFT OUTER JOIN account c ON b.cAcctNo=c.cAcctNo and b.cCompanyID=c.cCompanyID
		where a.cCompanyID=@cCompanyID and a.cTranNo=@cTranNo
END



GO


